package me.magicall.db.springjdbc.dao2;

import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import me.magicall.coll.ElementTransformerUtil.SerializableElementTransformer;
import me.magicall.db.dao.CommonDao;
import me.magicall.db.meta.DbColumn;
import me.magicall.db.meta.TableMeta;
import me.magicall.db.meta.TableMetaAccessor;
import me.magicall.db.util.DbUtil;
import me.magicall.db.util.ReturnKeyAndAffect;
import me.magicall.util.kit.Kits;

import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * 目前的限制：
 * model类和表的key必须是单字段，数据类型只能是Integer或Long
 * 
 * @author MaGiCalL
 * @param <T>
 */
public class CommonDaoImpl<T> implements CommonDao<T> {

	private TableMetaAccessor tableMetaAccessor;
	private NamedParameterJdbcOperations namedJdbc;

	public CommonDaoImpl(final TableMetaAccessor tableMetaAccessor, final NamedParameterJdbcOperations jdbc) {
		super();
		this.tableMetaAccessor = tableMetaAccessor;
		this.namedJdbc = jdbc;
	}

	//----------------------------------读方法 query methods
	@Override
	public Collection<T> fill(final Collection<T> modelsWithId) {
		if (Kits.COLL.isEmpty(modelsWithId)) {
			return modelsWithId;
		}

		final T first = modelsWithId.iterator().next();
		final Object firstId = getId(first);

		final Collection<T> coll;
		if (first instanceof Comparable) {
			coll = Kits.LIST.sort(modelsWithId);//for query faster
		} else if (firstId instanceof Comparable<?>) {//for query faster
			coll = Kits.LIST.sort(modelsWithId, new Comparator<T>() {
				@SuppressWarnings("unchecked")
				@Override
				public int compare(final T o1, final T o2) {
					@SuppressWarnings("rawtypes")
					final Comparable id1 = (Comparable<?>) getId(o1);
					@SuppressWarnings("rawtypes")
					final Comparable id2 = (Comparable<?>) getId(o2);
					return id1.compareTo(id2);
				}
			});
		} else {
			coll = modelsWithId;
		}

		final Object[] ids = new Object[coll.size()];
		final Map<Object, T> idMap = new HashMap<Object, T>();
		int index = 0;
		for (final T t : coll) {
			final Object id = getId(t);
			idMap.put(String.valueOf(id), t);
			ids[index++] = id;
		}

		final StringBuilder sb = DbUtil.buildSql_SelectAllFromWhereId(getTableName(first), getDbColumnNames(first), idColumnName());//
		Kits.STR.join(sb.append(" in("), modelsWithId, ",", MODLE_TO_ID_PLACE_HOLDER).append(")");

		final TableMeta tableMeta = getTableMetaUsingModel(idMap.get(String.valueOf(firstId)));
		return getNamedJdbc().query(sb.toString(), new SimpleSqlParameterSourceTemplate() {
			@Override
			public Object getValue(final String paramName) throws IllegalArgumentException {
				return ids[Kits.INT.fromString(paramName.substring(idFieldName().length()))];
			}
		}, new RowMapper<T>() {
			@Override
			public T mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
				final Object idFromResultSet = DbUtil.getIdFromResultSet(resultSet, tableMeta);//FIXME:这里可能有问题
				final T modelOnlyWithId = idMap.get(String.valueOf(idFromResultSet));
				return mappingRowWithoutId(modelOnlyWithId, resultSet, rowNum);
			}
		});
	}

	@Override
	public T fill(final T modelWithId) {
		final StringBuilder sb = DbUtil.buildSql_SelectAllFrom(getTableName(modelWithId), getDbColumnNames(modelWithId));
		appendWhereIdEqualsPlaceHolder(sb);
		try {
			return getNamedJdbc().queryForObject(sb.toString(), buildSqlParameterSource(modelWithId), new RowMapper<T>() {
				@Override
				public T mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					return mappingRowWithoutId(modelWithId, resultSet, rowNum);
				}
			});
		} catch (final EmptyResultDataAccessException e) {
			return null;
		}
	}

//========================================= 写方法 update methods 
	//------------------------------------- 多model方法 methods about multi models
	@Override
	public int[] add(final Collection<? extends T> models) {
		if (Kits.COLL.isEmpty(models)) {
			return Kits.INT.emptyPrimitiveArray();
		}
		return batchUpdate0(buildSql_Insert(getTableName(models), getDbColumnNames(models)).toString(), models);
	}

	@Override
	public ReturnKeyAndAffect addReturnKey(final Collection<? extends T> models) {
		final StringBuilder sb = new StringBuilder("insert into ").append(getTableName(models)).append("(");
		final List<String> columnNames = getDbColumnNames(models);
		Kits.STR.join(sb, columnNames, ",").append(")values");

		final Map<String, Object> namedParamValueMap = handleParamRef(models, columnNames, sb);
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final int changedCount = getNamedJdbc().update(sb.toString(), namedParamValueMap);
		return new ReturnKeyAndAffect(keyHolder.getKey(), changedCount);//FIXME:这里只有一个key
	}

	@Override
	public int addIfAbsent(final Collection<? extends T> models) {
		final List<String> columnNames = getDbColumnNames(models);
		final StringBuilder sb = new StringBuilder("insert ignore into ").append(getTableName(models)).append("(");
		Kits.STR.join(sb, columnNames, ",").append(")values");

		final Map<String, Object> namedParamValueMap = handleParamRef(models, columnNames, sb);
		return getNamedJdbc().update(sb.toString(), namedParamValueMap);
	}

	private Map<String, Object> handleParamRef(final Collection<? extends T> models, final List<String> columnNames, final StringBuilder sb) {
		final Map<String, Object> namedParamValueMap = new HashMap<String, Object>();
		boolean first = true;
		int i = 0;
		for (final T t : models) {
			if (first) {
				first = false;
			} else {
				sb.append(",");
			}
			sb.append("(");
			for (final String columnName : columnNames) {
				final String columnNameWithModelIndex = columnName + "#" + i;
				sb.append(DbUtil.namedParam(columnNameWithModelIndex)).append(",");
				namedParamValueMap.put(columnNameWithModelIndex, getProperty(t, columnName));
			}
			sb.deleteCharAt(sb.length() - 1).append(")");
			++i;
		}
		return namedParamValueMap;
	}

	@Override
	public int[] addOrUpdate(final Collection<? extends T> models) {
		if (Kits.COLL.isEmpty(models)) {
			return Kits.INT.emptyPrimitiveArray();
		}

		final List<String> dbColumnNames = getDbColumnNames(models);
		final StringBuilder sb = buildSql_Insert(getTableName(models), dbColumnNames).append("on duplicate key update ");
		buildSql_UpdateSetValuePart(dbColumnNames, sb);

		return batchUpdate0(sb.toString(), models);
	}

	@Override
	public int[] update(final Collection<? extends T> models) {
		if (Kits.COLL.isEmpty(models)) {
			return Kits.INT.emptyPrimitiveArray();
		}

		final StringBuilder sb = new StringBuilder("update ").append(getTableName(models)).append(" set ");
		final List<String> dbColumnNames = getDbColumnNames(models);
		buildSql_UpdateSetValuePart(dbColumnNames, sb);
		appendWhereIdEqualsPlaceHolder(sb);

		return batchUpdate0(sb.toString(), models);
	}

	@Override
	public int[] del(final Collection<? extends T> models) {
		if (Kits.COLL.isEmpty(models)) {
			return Kits.INT.emptyPrimitiveArray();
		}

		final StringBuilder sb = new StringBuilder("delete from ").append(getTableName(models));//
		appendWhereIdEqualsPlaceHolder(sb);

		return batchUpdate0(sb.toString(), models);
	}

	//---------------------------------- 单model方法 single model handling method
	@Override
	public boolean add(final T model) {
		final StringBuilder sb = buildSql_Insert(getTableName(model), getDbColumnNames(model));
		return getNamedJdbc().update(sb.toString(), buildSqlParameterSource(model)) > 0;
	}

	@Override
	public ReturnKeyAndAffect addReturnKey(final T model) {
		final List<String> columnNames = getDbColumnNames(model);
		final StringBuilder sb = new StringBuilder("insert into ").append(getTableName(model)).append("(");
		Kits.STR.join(sb, columnNames, ",");
		sb.append(")values(");
		Kits.STR.join(sb, columnNames, ",", FIELD_TO_PLACE_HOLDER);
		sb.append(")");
		return add(sb.toString(), model);
	}

	@Override
	public int update(final T model) {
		final StringBuilder sb = new StringBuilder("update ").append(getTableName(model)).append(" set ");
		final List<String> dbColumnNames = getDbColumnNames(model);
		buildSql_UpdateSetValuePart(dbColumnNames, sb);
		appendWhereIdEqualsPlaceHolder(sb);

		return getNamedJdbc().update(sb.toString(), buildSqlParameterSourceCheckingNull(model));
	}

	@Override
	public boolean addOrUpdate(final T model) {
		final List<String> dbColumnNames = getDbColumnNames(model);
		final StringBuilder sb = buildSql_Insert(getTableName(model), dbColumnNames).append(" on duplicate key update ");
		buildSql_UpdateSetValuePart(dbColumnNames, sb);
		final int rows = getNamedJdbc().update(sb.toString(), buildSqlParameterSourceCheckingNull(model));
		return rows > 0;
	}

	@Override
	public int del(final T model) {
		final StringBuilder sb = new StringBuilder("delete from ").append(getTableName(model));//
		appendWhereIdEqualsPlaceHolder(sb);

		return getNamedJdbc().update(sb.toString(), buildSqlParameterSource(model));
	}

	//-----------------------使用其他同名方法实现的重载方法 methods ref to other method with same name 
	@Override
	public Collection<T> fill(final T... modelOnlyWithIds) {
		return fill(asList(modelOnlyWithIds));
	}

	@Override
	public int[] add(final T... models) {
		return add(asList(models));
	}

	@Override
	public ReturnKeyAndAffect addReturnKey(final T... models) {
		return addReturnKey(asList(models));
	}

	@Override
	public int addIfAbsent(final T... models) {
		return addIfAbsent(asList(models));
	}

	@Override
	public int[] addOrUpdate(final T... models) {
		return addOrUpdate(asList(models));
	}

	@Override
	public int[] update(final T... models) {
		return update(asList(models));
	}

	@Override
	public int[] del(final T... models) {
		return del(asList(models));
	}

//=========================================
	protected int[] batchUpdate0(final String sql, final Collection<? extends T> models) {
		final SqlParameterSource[] sqlParameterSources = new SqlParameterSource[models.size()];
		int i = 0;
		for (final T model : models) {
			sqlParameterSources[i] = buildSqlParameterSourceCheckingNull(model);
			++i;
		}
		return getNamedJdbc().batchUpdate(sql, sqlParameterSources);
	}

	protected final Object getId(final T model) {
		return getProperty(model, idFieldName());
	}

	protected final List<T> asList(final T... models) {
		return Arrays.asList(models);
	}

	protected final Object getProperty(final T model, final DbColumn dbColumn) {
		return getProperty(model, DbUtil.dbNameToJavaName(dbColumn.getName()));
	}

	protected StringBuilder appendWhereIdEqualsPlaceHolder(final StringBuilder sb) {
		return sb.append(" where ").append(idColumnName()).append("=").append(placeHolderForId());
	}

	protected String placeHolderForId() {
		return DbUtil.namedParam(idFieldName());
	}

	protected String idFieldName() {
		return DbUtil.COMMON_ID_FIELD_NAME;
	}

	protected String idColumnName() {
		return DbUtil.COMMON_ID_COLUMN_NAME;
	}

	protected String getTableName(final Collection<? extends T> models) {
		return getTableName(models.iterator().next());
	}

	protected List<String> getDbColumnNames(final Collection<? extends T> models) {
		return getDbColumnNames(models.iterator().next());
	}

	protected void buildSql_UpdateSetValuePart(final List<String> dbColumnNames, final StringBuilder sb) {
		DbUtil.buildSql_UpdateSetValuePart(sb, dbColumnNames, FIELD_TO_PLACE_HOLDER, true);
	}

	protected StringBuilder buildSql_Insert(final String tableName, final List<String> dbColumnNames) {
		return DbUtil.buildSql_Insert(dbColumnNames, tableName, FIELD_TO_PLACE_HOLDER);
	}

	protected final SerializableElementTransformer<T, String> MODLE_TO_ID_PLACE_HOLDER = new SerializableElementTransformer<T, String>() {
		private static final long serialVersionUID = 5362030051196982123L;

		@Override
		public String transform(final int index, final T element) {
			return placeHolderForId() + index;
		}
	};
	protected final SerializableElementTransformer<String, String> FIELD_TO_PLACE_HOLDER = new SerializableElementTransformer<String, String>() {
		private static final long serialVersionUID = -4829831102981224486L;

		@Override
		public String transform(final int index, final String element) {
			return DbUtil.namedParam(DbUtil.dbNameToJavaName(element));
		}
	};

	protected T mappingRowWithoutId(final T modelOnlyWithId, final ResultSet resultSet, final int rowNum) throws SQLException {
		final ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
		final int columnCount = resultSetMetaData.getColumnCount();
		for (int i = DbUtil.RESULT_SET_COLUMN_START_INDEX; i <= columnCount; ++i) {
			final String label = resultSetMetaData.getColumnLabel(i);
			if (idFieldName().equalsIgnoreCase(label)) {//id 字段不再设
				continue;
			}
			setProperty(modelOnlyWithId, DbUtil.dbNameToJavaName(label), resultSet.getObject(i));
		}
		return modelOnlyWithId;
	}

	protected TableMeta getTableMetaUsingModelClass(final Class<?> modelClass) {
		return getTableMetaAccessor().getTableMetaUsingModelName(modelClass.getSimpleName());
	}

	protected TableMeta getTableMetaUsingModel(final T model) {
		return getTableMetaAccessor().getTableMetaUsingModelName(model.getClass().getSimpleName());
	}

//	protected TableMeta getTableMetaUsingModels(final List<T> list) {
//		return getTableMetaUsingModel(list.get(0));
//	}

	protected String getTableName(final T model) {
		return getTableMetaUsingModel(model).getName();
	}

	protected List<String> getDbColumnNames(final T model) {
		return getTableMetaUsingModel(model).getColumnNames();
	}

	protected Object getProperty(final Object model, final String fieldName) {
		try {
			return PropertyUtils.getProperty(model, fieldName);
		} catch (final IllegalAccessException e) {
			e.printStackTrace();
		} catch (final InvocationTargetException e) {
			e.printStackTrace();
		} catch (final NoSuchMethodException e) {//some field in db not in bean
			e.printStackTrace();
		}
		return null;
	}

	protected void setProperty(final Object model, final String fieldName, final Object value) {
		try {
			PropertyUtils.setProperty(model, fieldName, value);
		} catch (final IllegalAccessException e) {
			e.printStackTrace();
		} catch (final InvocationTargetException e) {
			e.printStackTrace();
		} catch (final NoSuchMethodException e) {
			e.printStackTrace();
		}
	}

	private abstract class SimpleSqlParameterSourceTemplate implements SqlParameterSource {
		@Override
		public boolean hasValue(final String fieldName) {
			return getValue(fieldName) != null;
		}

		@Override
		public String getTypeName(final String paramName) {
			return null;
		}

		@Override
		public int getSqlType(final String paramName) {
			return Integer.MIN_VALUE;
		}
	}

	protected SqlParameterSource buildSqlParameterSourceCheckingNull(final T model) {
		return new SimpleSqlParameterSourceTemplate() {
			@Override
			public Object getValue(final String fieldName) throws IllegalArgumentException {
				final Object value = getProperty(model, fieldName);
				return value == null ? defValue(model, fieldName) : value;
			}
		};
	}

	protected SqlParameterSource buildSqlParameterSource(final T model) {
		return new SimpleSqlParameterSourceTemplate() {
			@Override
			public Object getValue(final String fieldName) throws IllegalArgumentException {
				return getProperty(model, fieldName);
			}
		};
	}

	protected ReturnKeyAndAffect add(final String sql, final T model) {
		final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		final int changedCount = getNamedJdbc().update(sql, buildSqlParameterSourceCheckingNull(model), keyHolder);
		final Number key = keyHolder.getKey();
		setProperty(model, idFieldName(), key);
		return new ReturnKeyAndAffect(key, changedCount);
	}

	private Object defValue(final T model, final String fieldName) {
		final TableMeta tableMeta = getTableMetaUsingModel(model);
		final String columnName = DbUtil.javaNameToDbName(fieldName);
		final List<DbColumn> columns = tableMeta.getColumns();
		for (final DbColumn column : columns) {
			if (columnName.equalsIgnoreCase(column.getName())) {
				return column.getDefaultValue();
			}
		}
		return null;
	}

//======================================= getters & setters
	public TableMetaAccessor getTableMetaAccessor() {
		return tableMetaAccessor;
	}

	public void setTableMetaAccessor(final TableMetaAccessor tableMetaAccessor) {
		this.tableMetaAccessor = tableMetaAccessor;
	}

	public void setNamedJdbc(final NamedParameterJdbcOperations jdbc) {
		this.namedJdbc = jdbc;
	}

	public JdbcOperations getJdbcOperations() {
		return getNamedJdbc().getJdbcOperations();
	}

	public NamedParameterJdbcOperations getNamedJdbc() {
		return namedJdbc;
	}

}
